
package com.scalar.chiptrack.operations.process.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.forms.MarkAndPackForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;

public class MarkAndPackDAO {

    
    public static MarkAndPackForm loadMarkAndPackData (int processStepId) throws ScalarException {
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        
        MarkAndPackForm markAndPackData = null;
        
        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.markAndPack.loadMarkAndPackData"));
            pstmt.setInt (1, processStepId);
            
            rset = pstmt.executeQuery();

            if (rset.next()) {
                markAndPackData = new MarkAndPackForm (rset.getFloat  ("MPT_Standard_Unit_Cost"),
                                                         rset.getString ("MPT_PO_Frequency"),
                                                         rset.getFloat  ("MPT_Target_Yield"),
                                                         rset.getFloat  ("MPT_Lot_Scrap_Yield_Limit"),
                                                         rset.getString ("MPT_Visual_System"),
                                                         rset.getString ("MPT_Packing_Method"),
                                                         rset.getString ("Procedure_Doc_Number"),
                                                         rset.getString ("PDT_File_Name"),                                          
                                                         rset.getString ("MDT_Diagram"),
                                                         rset.getString ("MDT_File_Name"),
                                                         rset.getString ("Part_Number"),
                                                         rset.getInt    ("P_Parent_Product_ID"),
                                                         rset.getString ("PS_Type"), 
                                                         rset.getString ("PS_Comments"),
                                                         rset.getInt    ("previousStep_ID"));
            }
            if( markAndPackData != null )
            {
				markAndPackData.setDupProductName(ProductDao.loadProductDupNames( processStepId ));
            }
			  
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not LevelAccess list from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return markAndPackData;
    }
    
    
    public static void storeMarkAndPackData (DBTransaction dbTransaction,
                                                 int processStepId,
                                                 double standardUnitCost,
                                                 String poFrequency,
                                                 double targetYield,
                                                 double lotScrapYieldLimit,
                                                 int procedureDocId,
                                                 String visualSystem,
                                                 int markingDiagId,
                                                 String packingMethod) throws ScalarException {
    
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.markAndPack.insertNewRow"));

            pstmt.setInt (1, processStepId);
            pstmt.setDouble (2, standardUnitCost);
            pstmt.setString (3, poFrequency);
            pstmt.setDouble (4, targetYield);
            pstmt.setDouble (5, lotScrapYieldLimit);
            pstmt.setInt (6, procedureDocId);
            pstmt.setString (7, visualSystem);
            pstmt.setInt (8, markingDiagId);
            pstmt.setString (9, packingMethod);
            
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert a row into Mark_Pack_Table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }

	//Update Mark and Pack Data added by Raj Vallamsetla

	public static void updateMarkAndPackData (DBTransaction dbTransaction,
                                                 int processStepId,
                                                 double standardUnitCost,
                                                 String poFrequency,
                                                 double targetYield,
                                                 double lotScrapYieldLimit,
                                                 int procedureDocId,
                                                 String visualSystem,
                                                 int markingDiagId,
                                                 String packingMethod) throws ScalarException {
    
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("opearations.markAndPack.update"));

            pstmt.setDouble (1, standardUnitCost);
            pstmt.setString (2, poFrequency);
            pstmt.setDouble (3, targetYield);
            pstmt.setDouble (4, lotScrapYieldLimit);
            pstmt.setInt (5, procedureDocId);
            pstmt.setString (6, visualSystem);
            pstmt.setInt (7, markingDiagId);
            pstmt.setString (8, packingMethod);
			pstmt.setInt (9, processStepId);
            
            
            pstmt.executeUpdate();
            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Mark_Pack_Table (preocess stepID = " + processStepId + ")", sqle);
        }
        finally {
            
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }   
    }

}
